﻿/* Copy Write: AulacSoft */
/* Author write: Thangnc */
/* Code date: 12/17/2010 */

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using Aulac.Global.Xml;
using Aulac.Srv.Db.Data.Ems.InvJrns;
using Aulac.Rsc;
using Aulac.Rsc.Ems.Tags;
using Aulac.Global.Common;
using Aulac.Srv.Db.Common;
using Aulac.Rsc.Ems;
using System.Data;
using Aulac.Global.Exceptions;
using Aulac.ErrCodes;
using Aulac.Srv.Db.Data.Ems.Global;
using Aulac.Srv.Db.Data.Ems.AcntJrns.Xml;

namespace Aulac.Srv.Db.Data.Ems.Stores.InOutputStores
{
    partial class VchRecStoreMng
    {

        private string GetNewVchNoDb(string pVchType, ref string pVchName)
        {
            /* Code date: 12/17/2010 by Thangnc*/

            return GetNewVchNo(VchRecStoreInfo.VchRecInvHeader, tagVch.Inv.VchRecInvPrkID, pVchType, ref pVchName);
            //sqlQuery = string.Format("SELECT TOP 1 v.{0}  FROM {1} v ", tagVch.Inv.VchNo, TableNames.VchRecInvHeader);
            ////sqlQuery += string.Format(" LEFT JOIN {0} t ON v.{1} = t.{2}", TableNames.Dm_VchTypes, tagVch.Inv.VchTypeID, tagVch.Inv.VchTypeID);
            //sqlQuery += string.Format(" WHERE ( {0} BETWEEN ? AND ? ) AND (v.{1} = ?)", tagVch.Inv.VchDate, tagVch.Inv.VchTypeID);
            //sqlQuery += string.Format(" ORDER BY {0} DESC, {1} DESC", tagVch.Inv.VchDate, tagVch.Inv.VchRecInvPrkID);

            //string str = DbDataProvider.SelectValueFromDb(sqlQuery, this.DateBeginFnc, this.DateEndFnc, pVchType);
            //pVchName = DbVchTypeInfo.GetVchName(DbDataProvider, pVchType);
            //if (Aulac.Global.Common.ValueHelper.isEmpty(str))
            //{
            //    VchTypes enumVchtype = (VchTypes)ValueHelper.ChangeType(pVchType, typeof(VchTypes));
            //    if (enumVchtype == VchTypes.VchRecvMdnStore)
            //        str = "00/PNK";
            //    else if (enumVchtype.Equals(VchTypes.VchRelsMdnStore))
            //        str = "00/PXK";
            //    else
            //        str = "00/PCK";
            //}

            //return CreateNewVoucherNo(str);
        }

        private bool DelVchStoreDb(string pVchRecPrkID, string pVchTypeID, bool pIsCheckLostQty)
        {
            /* Code date: 12/17/2010 by Thangnc*/

            DelVchStoreLineDb(pVchRecPrkID);
            DelVchStoreHeader(pVchRecPrkID);
            XmlDataDocument xmlQtyLost = null;
            if (!DelInvJournal(pVchRecPrkID, pVchTypeID, pIsCheckLostQty, out xmlQtyLost) && xmlQtyLost != null)
                throw new AulacException(ErrorCode.Error0010, xmlQtyLost.OuterXml, "");

            return true;
        }

        private string GetVchStorePrkID(string pVoucherOrgID, string pVoucherType)
        {
            /* Code date: 12/17/2010 by Thangnc*/

            sqlQuery = string.Format("SELECT {0} FROM {1}", tagVch.Inv.VchRecInvPrkID, TableNames.VchRecInvHeader);
            sqlQuery += string.Format(" WHERE {0} = ? AND {1} = ?", tagVch.Inv.VchOrgID, tagVch.Inv.VchTypeID);
            return DbDataProvider.SelectValueFromDb(sqlQuery, pVoucherOrgID, pVoucherType);
        }

        private XmlDataDocument GetVchStoreDb(string pStoreMdnVPrkID)
        {
            /* Code date: 12/17/2010 by Thangnc*/

            string str = StringHelper.ConvertArrayToString(", h.", VchRecStoreInfo.mListRecVchH.ToArray());
            str = "h." + str;
            string str1 = StringHelper.ConvertArrayToString(", l.", VchRecStoreInfo.mListRecVchL.GetRange(1, VchRecStoreInfo.mListRecVchL.Count - 1).ToArray());
            str1 = "l." + str1;
            string str3 = string.Format(" m.{0}, m.{1}, m.{2},m.{3}", tagVch.Inv.ItemName, tagVch.Inv.Unit, tagVch.Inv.ItemID, tagItems.Item.ItemGrpID);
            sqlQuery = string.Format("SELECT {0}, {1}, {2} FROM {3} h INNER JOIN {4} l", str, str1, str3, VchRecStoreInfo.VchRecInvHeader, VchRecStoreInfo.VchRecInvLine);
            sqlQuery += string.Format(" ON h.{0} = l.{1}", tagVch.Inv.VchRecInvPrkID, tagVch.Inv.VchRecInvPrkID);
            sqlQuery += string.Format(" INNER JOIN {0} m ON l.{1}  = m.{2}", TableNames.Dm_MdnItems, tagVch.Inv.ItemPrkID, tagVch.Inv.ItemPrkID);
            sqlQuery += string.Format(" WHERE (h.{0} = ?)", tagVch.Inv.VchRecInvPrkID);

            DbDataReader = DbDataProvider.ExecuteReader(mDbCmd, sqlQuery, pStoreMdnVPrkID);
            XmlDataDocument xmlDoc = CreateXmlDocument("", "");
            XmlElement eHeader = null;
            XmlElement eVoucher = XmlProcess.AddVchElm(ref xmlDoc);
            while (DbDataReader.Read())
            {
                if (eHeader == null)
                {
                    eHeader = XmlProcess.AddVchHeaderElm(ref eVoucher);
                    DbDataProvider.GetValuesToXml(DbDataReader, ref eHeader, VchRecStoreInfo.mListRecVchH.ToArray());
                }
                XmlElement eLine = XmlProcess.AddVchLineItemElm(eVoucher);
                DbDataProvider.GetValuesToXml(DbDataReader, ref eLine, VchRecStoreInfo.mListRecVchL.ToArray());
                DbDataProvider.GetValuesToXml(DbDataReader, ref eLine, tagVch.Inv.ItemName, tagVch.Inv.Unit, tagVch.Inv.ItemID, tagItems.Item.ItemGrpID);
            }
            DbDataReader.Close();
            return xmlDoc;
        }

        private bool AddVchStoreHeader(XmlElement eVch, ref string pRecVchPrkID, ref string pVchType)
        {
            /* Code date: 12/17/2010 by Thangnc*/

            XmlElement eHeader = XmlProcess.GetVchHeaderElm(eVch);
            pRecVchPrkID = XmlProcess.GetValueAtrb(eHeader, tagVch.Inv.VchRecInvPrkID);
            pVchType = XmlProcess.GetValueAtrb(eHeader, tagVch.Inv.VchTypeID);

            //string sVchDate = XmlProcess.GetValueVchHeader(xmlDoc, tagVch.Inv.VchDate);
            string sVchNo = XmlProcess.GetValueAtrb(eHeader, tagVch.Inv.VchNo);
            if (Aulac.Global.Common.ValueHelper.isEmpty(sVchNo) ||IsExistVchNo(pVchType, sVchNo))
            {
                string sVchName = "";
                sVchNo = GetNewVchNoDb(pVchType, ref sVchName);
                if (ValueHelper.isEmpty(sVchName))
                    sVchName = XmlProcess.GetValueVchHeader(eHeader, tagVch.Inv.VchName);
                XmlProcess.SetAtrb(ref eHeader, tagVch.Inv.VchNo, sVchNo);
                XmlProcess.SetAtrb(ref eHeader, tagVch.Inv.VchName, sVchName);
            }

            string[] arr = VchRecStoreInfo.mListRecVchH.GetRange(1, VchRecStoreInfo.mListRecVchH.Count - 1).ToArray();
            sqlQuery = SqlHelper.CreateSqlInsert(TableNames.VchRecInvHeader, arr);

            DbDataProvider.AddParamToCmd(mDbCmd, eHeader, arr);
            if (DbDataProvider.ExecuteNonQuery(ref mDbCmd, sqlQuery) <= 0)
                return false;

            pRecVchPrkID = DbDataProvider.SelectMaxID(mDbCmd, TableNames.VchRecInvHeader, tagVch.Inv.VchRecInvPrkID);
            XmlProcess.SetAtrb(ref eHeader, tagVch.Inv.VchRecInvPrkID, pRecVchPrkID);
            return true;
        }

        private bool UpdateVchStoreHeader(XmlElement eVch, ref string pVchRecPrkID, ref string pVchTypeID)
        {
            /* Code date: 12/17/2010 by Thangnc*/

            XmlElement eHeader = XmlProcess.GetVchHeaderElm(eVch);
            pVchRecPrkID = XmlProcess.GetValueAtrb(eHeader, tagVch.Inv.VchRecInvPrkID);
            string sVchDate = XmlProcess.GetValueAtrb(eHeader, tagVch.Inv.VchDate);
            pVchTypeID = XmlProcess.GetValueAtrb(eHeader, tagVch.Inv.VchTypeID);

            string[] arr = VchRecStoreInfo.mListRecVchH.GetRange(1, VchRecStoreInfo.mListRecVchH.Count - 1).ToArray();
            sqlQuery = SqlHelper.CreateSqlUpdate(TableNames.VchRecInvHeader, arr);
            sqlQuery += string.Format(" WHERE {0} = ?", tagVch.Inv.VchRecInvPrkID);
            DbDataProvider.AddParamToCmd(mDbCmd, eHeader, arr);
            DbDataProvider.AddParamToCmd(mDbCmd, false, pVchRecPrkID);

            return DbDataProvider.ExecuteNonQuery(ref mDbCmd, sqlQuery) > 0;
        }

        private bool AddVListStoreLine(XmlNodeList pListLine, string pStoreMdnVPrkID)
        {
            /* Code date: 12/17/2010 by Thangnc*/

            if (null == pListLine)
                throw new AulacException(this, "AddVListStoreLine", "No Load data document!");
            try
            {
                sqlQuery = SqlHelper.CreateSqlInsert(TableNames.VchRecInvLine, VchRecStoreInfo.mListRecVchL.ToArray());

                for (int i = 0; i < pListLine.Count; i++)
                {
                    XmlElement eLine = pListLine[i] as XmlElement;
                    XmlProcess.SetAtrb(ref eLine, tagVch.Inv.VchRecInvPrkID, pStoreMdnVPrkID);
                    XmlProcess.SetAtrb(ref eLine, tagVch.Inv.ItemLine, (i + 1));

                    DbDataProvider.AddParamToCmd(mDbCmd, eLine, VchRecStoreInfo.mListRecVchL.ToArray());
                    DbDataProvider.ExecuteNonQuery(ref mDbCmd, sqlQuery);
                }
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private bool DelVchStoreLineDb(string pVoucherID)
        {
            /* Code date: 12/17/2010 by Thangnc*/

            sqlQuery = SqlHelper.CreateSqlDelete(VchRecStoreInfo.VchRecInvLine, tagVch.Inv.VchRecInvPrkID);
            return DbDataProvider.ExecuteNonQuery(ref mDbCmd, sqlQuery, pVoucherID) >= 0;
        }

        private bool DelVchStoreHeader(string pStoreMdnVPrkID)
        {
            /* Code date: 12/17/2010 by Thangnc*/

            sqlQuery = SqlHelper.CreateSqlDelete(VchRecStoreInfo.VchRecInvHeader, tagVch.Inv.VchRecInvPrkID);
            return DbDataProvider.ExecuteNonQuery(ref mDbCmd, sqlQuery, pStoreMdnVPrkID) >= 0;
        }

        private void GetVchStoreDb(ref DataTable pDataTable, string pVchRecInvPrkID)
        {
            /* Code date: 12/17/2010 by Thangnc*/

            string str = StringHelper.ConvertArrayToString(", h.", VchRecStoreInfo.mListRecVchH.ToArray());
            str = "h." + str;
            string str1 = StringHelper.ConvertArrayToString(", l.", VchRecStoreInfo.mListRecVchL.GetRange(1, VchRecStoreInfo.mListRecVchL.Count - 1).ToArray());
            str1 = "l." + str1;
            //string str3 = string.Format(" m.{0}, m.{1}, m.{2}", tagVch.Inv.ItemName, tagVch.Inv.Unit, tagVch.Inv.ItemID);
            sqlQuery = string.Format("SELECT {0}, {1} FROM {2} h INNER JOIN {3} l", str, str1, TableNames.VchRecInvHeader, TableNames.VchRecInvLine);
            sqlQuery += string.Format(" ON h.{0} = l.{1}", tagVch.Inv.VchRecInvPrkID, tagVch.Inv.VchRecInvPrkID);
            //sqlQuery += string.Format("  INNER JOIN {0} ON {1} = {2}", TableNames.Dm_MdnItems, tagVch.Inv.ItemPrkID, tagVch.Inv.ItemPrkID);
            sqlQuery += string.Format(" WHERE (h.{0} = ?)", tagVch.Inv.VchRecInvPrkID);

            DbDataProvider.ExecuteReaderDataToTable(mDbCmd, sqlQuery, ref pDataTable, pVchRecInvPrkID);
        }

 
        private XmlDataDocument CreateXmlAcntJrn(string pVchRecInvPrkID)
        {
            /* Code date: 12/17/2010 by Thangnc*/

            XmlDataDocument xmlAcnt = CreateXmlDocument("", "");

            XmlElement eHeader = null;

            XmlElement eAcntJe = XmlAcntProcess.AddAcntJe(ref xmlAcnt);
            XmlElement eJeLines = XmlAcntProcess.AddJeLines(ref eAcntJe);
            XmlElement eAcntGrp = null;
            XmlElement eLine = null;
            DataTable tb = null;
            GetVchStoreDb(ref tb, pVchRecInvPrkID);
            if (tb == null || tb.Rows.Count == 0)
                return null;

            eAcntGrp = XmlAcntProcess.AddAcntGrps(eJeLines);
            double dTtlAmt = 0;
            foreach (DataRow row in tb.Rows)
            {
                if (eHeader == null)
                {
                    eHeader = XmlAcntProcess.AddJeHeader(ref xmlAcnt);
                    XmlProcess.SetAtrb(ref eHeader, tagVch.Inv.VchOrgID, Aulac.Global.Data.DataTableProcess.GetValue(row, tagVch.Inv.VchRecInvPrkID));
                    XmlProcess.SetAtrb(ref eHeader, tagVch.Inv.VchTypeID, Aulac.Global.Data.DataTableProcess.GetValue(row, tagVch.Inv.VchTypeID));
                    XmlProcess.SetAtrb(ref eHeader, tagVch.Inv.VchDate, Aulac.Global.Data.DataTableProcess.GetValue(row, tagVch.Inv.VchDate));
                    XmlProcess.SetAtrb(ref eHeader, tagVch.Inv.VchNo, Aulac.Global.Data.DataTableProcess.GetValue(row, tagVch.Inv.VchNo));
                }
                //Ghi no
                eLine = XmlAcntProcess.AddAcntLine(eAcntGrp);
                //XmlProcess.SetAtrb(ref eLine, tagAcnt.AcntID, AcntCoa.Acnt156);
                XmlProcess.SetAtrb(ref eLine, tagAcnt.ItemPrkID, Aulac.Global.Data.DataTableProcess.GetValue(row, tagVch.Inv.ItemPrkID));
                XmlProcess.SetAtrb(ref eLine, tagVch.Inv.Quantity, Aulac.Global.Data.DataTableProcess.GetValueNumber(row, tagVch.Inv.Quantity));
                dTtlAmt += Aulac.Global.Data.DataTableProcess.GetValueNumber(row, tagVch.Inv.AmtTtlLineInExc);
                XmlProcess.SetAtrb(ref eLine, tagAcnt.AmtLineInExc, Aulac.Global.Data.DataTableProcess.GetValueNumber(row, tagVch.Inv.AmtTtlLineInExc));
                /*
                //Thuoc tinh doi tuong
                List<AcntAtrbObj> list = AcntHelper.GetListAcntAtrbObj(DbDataProvider, AcntCoa.Acnt156);
                if (list.Count > 0)
                {
                    eAtrbObj = XmlAcntProcess.AddAcntAtrbInfo(ref eLine);
                    foreach (AcntAtrbObj dm in list)
                    {
                        if (dm.IsAllowNull)
                        {
                            if (tb.Columns.IndexOf(dm.FieldData) < 0)
                                continue;
                        }
                        eAtrbLine = XmlAcntProcess.AddAcntAtrb(ref eAtrbObj);
                        XmlProcess.SetAtrb(ref eAtrbLine, tagAcnt.AcntAtrbPrkID, dm.AcntAtrbPrkID);
                        XmlProcess.SetAtrb(ref eAtrbLine, tagAcnt.ObjTypePrkID, dm.ObjTypePrkID);
                        XmlProcess.SetAtrb(ref eAtrbLine, tagAcnt.ObjPrkID, Aulac.Global.Data.DataTableProcess.GetValue(row, dm.FieldData));
                    }
                }
                 */
            }

            /* eLine = XmlAcntProcess.AddAcntLine(eAcntGrp);
       XmlProcess.SetAtrb(ref eLine, tagAcnt.AcntID, AcntCoa.Acnt331);
       XmlProcess.SetAtrb(ref eLine, tagAcnt.AmtLineInExc, -dTtlAmt);
       List<AcntAtrbObj> listCsp = AcntHelper.GetListAcntAtrbObj(DbDataProvider, AcntCoa.Acnt331);
       if (listCsp.Count > 0)
       {
           eAtrbObj = XmlAcntProcess.AddAcntAtrbInfo(ref eLine);
           foreach (AcntAtrbObj dm in listCsp)
           {
               eAtrbLine = XmlAcntProcess.AddAcntAtrb(ref eAtrbObj);
               XmlProcess.SetAtrb(ref eAtrbLine, tagAcnt.AcntAtrbPrkID, dm.AcntAtrbPrkID);
               XmlProcess.SetAtrb(ref eAtrbLine, tagAcnt.ObjTypePrkID, dm.ObjTypePrkID);
               XmlProcess.SetAtrb(ref eAtrbLine, tagAcnt.ObjPrkID, Aulac.Global.Data.DataTableProcess.GetValue(tb.Rows[0], dm.FieldData));
           }
       }
            */
            return xmlAcnt;
        }

        private bool IsExistVchNo(string sVchTypeID, string pVchNo)
        {
            /* Code date: 12/17/2010 by Thangnc*/

            sqlQuery = string.Format("SELECT TOP 1 {0} FROM {1}", tagVch.Inv.VchNo, VchRecStoreInfo.VchRecInvHeader);
            sqlQuery += string.Format(" WHERE ({0} = ?) AND ({1} = ?)AND ({2} BETWEEN ? AND ?)", tagVch.Inv.VchTypeID, tagVch.Inv.VchNo, tagVch.Inv.VchDate);
            return !ValueHelper.isEmpty(DbDataProvider.ExecuteScalar(mDbCmd, sqlQuery, sVchTypeID, pVchNo, DateBeginFnc, DateEndFnc));

        }
        #region InvJournal Methods
        private XmlDataDocument CreateXmlInvJournal(XmlDataDocument xmlStore)
        {
            /* Code date: 12/17/2010 by Thangnc*/

            XmlDataDocument xmlDoc = CreateXmlDocument("", "");
            XmlElement eHeader = XmlProcess.GetVchHeaderElm(xmlStore);
            XmlElement eInvJrn = XmlInvJrnHelper.AddInvJrnElm(xmlDoc);
            XmlElement eInvHeader = XmlInvJrnHelper.AddInvJrnHeader(eInvJrn);

            TagStores sTagStore = (TagStores)ValueHelper.ChangeType(XmlProcess.GetValueAtrb(eHeader, tagVch.Inv.TagStore), typeof(TagStores));
            string sStorePrkID = XmlProcess.GetValueAtrb(eHeader, tagVch.Inv.StorePrkID);
            XmlProcess.SetAtrb(ref eInvHeader, tagVch.Inv.InvJrnPrkID, "");
            XmlProcess.SetAtrb(ref eInvHeader, tagVch.Inv.VchOrgID, XmlProcess.GetValueAtrb(eHeader, tagVch.Inv.VchRecInvPrkID));
            XmlProcess.SetAtrb(ref eInvHeader, tagVch.Inv.VchOrgTypeID, XmlProcess.GetValueAtrb(eHeader, tagVch.Inv.VchTypeID));
            XmlProcess.SetAtrb(ref eInvHeader, tagVch.Inv.VchDate, XmlProcess.GetValueAtrb(eHeader, tagVch.Inv.VchDate));
            XmlProcess.SetAtrb(ref eInvHeader, tagVch.Inv.VchNo, XmlProcess.GetValueAtrb(eHeader, tagVch.Inv.VchNo));
            XmlProcess.SetAtrb(ref eInvHeader, tagVch.Inv.Note, XmlProcess.GetValueAtrb(eHeader, tagVch.Inv.Note));
            XmlProcess.SetAtrb(ref eInvHeader, tagVch.Inv.VchName, XmlProcess.GetValueAtrb(eHeader, tagVch.Inv.VchName));

            XmlNodeList listLine = XmlProcess.GetListVchLineItem(xmlStore);

            IEnumerable<XmlElement> l = listLine.Cast<XmlElement>();
            var result = from s in l.AsEnumerable()
                         group s by new
                         {
                             StorePrkID = XmlProcess.GetValueAtrb(s, tagVch.Inv.StorePrkID),
                             ItemPrkID = XmlProcess.GetValueAtrb(s, tagVch.Inv.ItemPrkID)
                         } into grpData
                         select new
                         {
                             grpData.Key,
                             Quantity = grpData.Sum(s => XmlProcess.GetValueDoubleAtrb(s, tagVch.Inv.Quantity)),
                             PrimeCostInExc = grpData.Sum(s => XmlProcess.GetValueDoubleAtrb(s, tagVch.Inv.Quantity) * XmlProcess.GetValueDoubleAtrb(s, tagVch.Inv.PrimeCostInExc)) / grpData.Sum(s => XmlProcess.GetValueDoubleAtrb(s, tagVch.Inv.Quantity))
                         };
            int lIndex = 0;
            foreach (var e in result)
            {
                XmlElement eLine = XmlInvJrnHelper.AddInvJrnItemOfInvJrn(eInvJrn);
                double dQty = e.Quantity;
                if (sTagStore == TagStores.OutputStore)
                    dQty = -dQty;
                XmlProcess.SetAtrb(ref eLine, tagVch.Inv.ItemLine, ++lIndex);
                string lStorePrkID = e.Key.StorePrkID;

                XmlProcess.SetAtrb(ref eLine, tagVch.Inv.StorePrkID, e.Key.StorePrkID);
                XmlProcess.SetAtrb(ref eLine, tagVch.Inv.ItemPrkID, e.Key.ItemPrkID);
                XmlProcess.SetAtrb(ref eLine, tagVch.Inv.Quantity, dQty);
                XmlProcess.SetAtrb(ref eLine, tagVch.Inv.PrimeCostInExc, e.PrimeCostInExc);
                XmlProcess.SetAtrb(ref eLine, tagVch.Inv.QtyOnhand, 0);
                XmlProcess.SetAtrb(ref eLine, tagVch.Inv.AmtOnhandInExc, 0);
                //XmlProcess.SetAtrb(ref eLine, tagVch.Inv.NoteLine, );
            }
            return xmlDoc;
        }

        private bool UpdateInvJournal(XmlDataDocument xmlDoc, out XmlDataDocument xmlQtyLost)
        {
            XmlDataDocument xmlInvStore = CreateXmlInvJournal(xmlDoc);

            InvJrnMng InvJrn = new InvJrnMng(this.DbDataProvider.DbAulacConnection);
            return InvJrn.SetInvJrn(xmlInvStore, out xmlQtyLost);
        }

        private bool DelInvJournal(string pVchOrgID, string pVchTypeID, bool pIsCheckLostQty, out XmlDataDocument xmlQtyLost)
        {
            InvJrnMng InvJrn = new InvJrnMng(DbDataProvider.DbAulacConnection);
            string sInvJrnPrkID = InvJrn.GetInvJrnPrkID(pVchOrgID, pVchTypeID);
            return InvJrn.DelIvnJrn(sInvJrnPrkID, pIsCheckLostQty, out xmlQtyLost);
        }

        #endregion
        
    }
}
